package com.itmk.teacher.dao.impl;

import com.itmk.common.BaseDao;
import com.itmk.major.entity.MajorTotal;
import com.itmk.student_point.dao.StudentPointDao;
import com.itmk.student_point.dao.impl.StudentPointDaoImpl;
import com.itmk.student_point.entity.StudentPoint;
import com.itmk.teacher.dao.TeacherDao;
import com.itmk.teacher.entity.*;
import com.itmk.user.entity.SysUser;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.apache.commons.lang.StringUtils;

import java.sql.SQLException;
import java.util.List;

public class TeacherDaoImpl extends BaseDao implements TeacherDao {
    private StudentPointDao studentPointDao = new StudentPointDaoImpl();
    @Override
    public void add(Teacher teacher) {
        //1、定义sql
        String sql = "insert into teacher(teacher_id,teacher_name,phone,sex,teacher_desc,teacher_num,password) values(?,?,?,?,?,?,?)";
        //2、定义handler
        BeanHandler<Teacher> handler = new BeanHandler<>(Teacher.class);
        //3、定义参数
        Object[] parms = {
                teacher.getTeacherId(),
                teacher.getTeacherName(),
                teacher.getPhone(),
                teacher.getSex(),
                teacher.getTeacherDesc(),
                teacher.getTeacherNum(),
                teacher.getPassword()
        };
        try {
            query.insert(sql, handler, parms);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    @Override
    public List<Teacher> list(TeacherParm parm) {
        //判断关键字
        if (StringUtils.isNotEmpty(parm.getTeacherName())) {
            //1、定义sql
            StringBuilder sb = new StringBuilder("select * from teacher");
            sb.append(" where teacher_name like '%" + parm.getTeacherName() + "%'");
            String sql = sb.toString();
            //2、定义handler
            BeanListHandler<Teacher> handler = new BeanListHandler<>(Teacher.class, processor);
            try {
                return query.query(sql, handler);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            return null;
        }else {
            StringBuilder sb = new StringBuilder("select * from  (select teacher.* , ROWNUM rm from teacher)");
            //分页
            sb.append(" where rm >= ? and rm <= ? ");
            String sql = sb.toString();
            //2、定义handler
            BeanListHandler<Teacher> handler = new BeanListHandler<>(Teacher.class, processor);
            //3、定义参数
            Object[] parms = {
                    (parm.getPage() - 1) * parm.getLimit(),
                    (parm.getLimit()*parm.getPage())
            };
            try {
                return query.query(sql, handler, parms);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            return null;

        }

    }

    @Override
    public int getCount(TeacherParm parm) {
        //1、定义sql
        StringBuilder sb = new StringBuilder("select count(teacher_id) from teacher");
        //判断关键字
        if (StringUtils.isNotEmpty(parm.getTeacherName())) {
            sb.append(" where teacher_name like '%" + parm.getTeacherName() + "%'");
        }
        String sql = sb.toString();
        //2、定义handler
        ScalarHandler handler = new ScalarHandler();
        try {
            return Integer.valueOf(query.query(sql, handler).toString());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return 0;
    }

    @Override
    public Teacher getById(int teacherId) {
        //1、定义sql
        String sql = "select * from teacher where teacher_id=?";
        //2、定义handler
        BeanHandler<Teacher> handler = new BeanHandler<>(Teacher.class, processor);
        try {
            return query.query(sql, handler, teacherId);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    @Override
    public void updateById(Teacher teacher) {
        //1、定义sql
        String sql = "update teacher set teacher_name=?,phone=?,sex=?,teacher_desc=?,teacher_num=?,password=? where " +
                "teacher_id=?";
        //2、定义参数
        Object[] parms = {
                teacher.getTeacherName(),
                teacher.getPhone(),
                teacher.getSex(),
                teacher.getTeacherDesc(),
                teacher.getTeacherNum(),
                teacher.getPassword(),
                teacher.getTeacherId()
        };
        try {
            query.update(sql, parms);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    @Override
    public void delete(String teacherId) {
        String sql = "delete from teacher where teacher_id=?";
        try {
            query.update(sql, teacherId);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    @Override
    public Teacher login(String teacherNum, String password) {
        //1、定义sql
        String sql = "select * from teacher where teacher_num=? and password=?";
        //2、定义handler
        BeanHandler<Teacher> handler = new BeanHandler<>(Teacher.class, processor);
        //3、定义参数
        Object[] parms = {
                teacherNum,
                password
        };
        try {
            return query.query(sql, handler, parms);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    @Override
    public List<TeacherVo> getTeachetList(TeacherCouresParm parm) {
        //处理关键字
        if (StringUtils.isNotEmpty(parm.getCourseName())) {
            //1.定义sql
            String sql = "select  * from (select cs.*,tc.teacher_name,mj.major_name ,ct.teacher_id,(select count(sta.student_course_id) from student_course sta\n" +
                    "where sta.course_id =ct.course_id and sta.teacher_id = ct.teacher_id) total from course_teacher ct\n" +
                    "left join course  cs on cs.course_id = ct.course_id left join teacher  tc on tc.teacher_id = ct.teacher_id\n" +
                    "left join major  mj on mj.major_id = cs.major_id  ) where teacher_id=?";
            StringBuilder sb = new StringBuilder(sql);
            sb.append(" and course_name like '%" + parm.getCourseName() + "%'");
            //2、定义handler
            BeanListHandler<TeacherVo> handler = new BeanListHandler<>(TeacherVo.class, processor);
            Object[] parms = {
                    parm.getTeacherId()
            };
            try {
                List<TeacherVo> query = this.query.query(sb.toString(), handler,parms);
                return query;
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }else {
            //1.定义sql
            String sql = "select  * from (select cs.*,tc.teacher_name,mj.major_name,ROWNUM rm ,ct.teacher_id,(select count(sta.student_course_id) from student_course sta\n" +
                    "where sta.course_id =ct.course_id and sta.teacher_id = ct.teacher_id) total from course_teacher ct\n" +
                    "left join course  cs on cs.course_id = ct.course_id left join teacher  tc on tc.teacher_id = ct.teacher_id\n" +
                    "left join major  mj on mj.major_id = cs.major_id ) where teacher_id=? ";
            StringBuilder sb = new StringBuilder(sql);
            //根据教师id查询
            sb.append(" and  rm >= ? and rm <= ?");
            //分页处理
            sql = sb.toString();
            //2、定义handler
            BeanListHandler<TeacherVo> handler = new BeanListHandler<>(TeacherVo.class, processor);
            //3、定义参数
            Object[] parms = {
                    parm.getTeacherId(),
                    (parm.getPage() - 1) * parm.getLimit(),
                    parm.getLimit()*parm.getPage()
            };
            try {
                return query.query(sql, handler, parms);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return null;
    }

    @Override
    public int getTeachetCount(TeacherCouresParm parm) {
        //定义sql
        String sq =("select count(ct.course_teacher_id) from course_teacher\n" +
                "    ct left join course  cs on cs.course_id = ct.course_id left join teacher  tc on tc.teacher_id =\n" +
                "    ct.teacher_id left join major  mj on mj.major_id = cs.major_id where tc.teacher_id = ? ");
        StringBuilder sb = new StringBuilder(sq);
        if (StringUtils.isNotEmpty(parm.getCourseName())) {
            //处理关键字
            sb.append("  and course_name like '%" + parm.getCourseName() +"%'");

        }
        String sql = sb.toString();

        //2、定义handler
        ScalarHandler handler = new ScalarHandler();
        Object[] parms = {
                parm.getTeacherId()
        };
        try {
            Integer value = Integer.valueOf(query.query(sql, handler, parms).toString());
            return  value;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        return 0;
    }

    @Override
    public List<TeacherStudentVo> listStu(TeacherParm parm) {
        //1、定义sql
        String sq = "select st.stu_id,stu_name,st.sex,st.phone,cls.calss_name  from  student_course as sc  left join " +
                "student as st on st.stu_id = sc.stu_id  left join classes as cls on st.calss_id = cls.calss_id  " +
                "where 1=1 ";
        StringBuilder sb = new StringBuilder(sq);
        //关键字查询
        if (StringUtils.isNotEmpty(parm.getStuName())) {
            sb.append(" and st.stu_name like '%" + parm.getStuName() + "%'");
        }
        //id
        sb.append(" and sc.course_id = ? and sc.teacher_id = ?");
        //分页处理
        sb.append(" limit ?,?");
        String sql = sb.toString();
        //2、定义handler
        BeanListHandler<TeacherStudentVo> handler = new BeanListHandler<>(TeacherStudentVo.class, processor);
        //3、定义参数
        Object[] parms = {
                parm.getCourseId(),
                parm.getTeacherId(),
                (parm.getPage() - 1) * parm.getLimit(),
                parm.getLimit()
        };
        try {
            return query.query(sql,handler,parms);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    @Override
    public List<TeacherStudentVo> listInputStu(TeacherParm parm) {
        //1、定义sql
        String sq = "select st.stu_id,sc.course_id,st.stu_name,sp.points from  student_course as sc  left join  " +
                "student as st on st.stu_id = sc.stu_id left join student_point as sp on sp.stu_id = st.stu_id and sp.course_id = sc.course_id  where 1=1";
        StringBuilder sb = new StringBuilder(sq);
        //关键字查询
        if (StringUtils.isNotEmpty(parm.getStuName())) {
            sb.append(" and st.stu_name like '%" + parm.getStuName() + "%'");
        }
        //id
        sb.append(" and sc.course_id = ? and sc.teacher_id = ?  order by st.stu_name asc");
        String sql = sb.toString();
        //2、定义handler
        BeanListHandler<TeacherStudentVo> handler = new BeanListHandler<>(TeacherStudentVo.class, processor);
        //3、定义参数
        Object[] parms = {
                parm.getCourseId(),
                parm.getTeacherId()
        };
        try {
            return query.query(sql,handler,parms);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    @Override
    public int getStuCount(TeacherParm parm) {
         //1、定义sql
        String sq = "select count(sc.student_course_id)  from  student_course as sc  left join " +
                "student as st on st.stu_id = sc.stu_id  left join classes as cls on st.calss_id = cls.calss_id  " +
                "where 1=1 ";
        StringBuilder sb = new StringBuilder(sq);
        //关键字查询
        if (StringUtils.isNotEmpty(parm.getStuName())) {
            sb.append(" and st.stu_name like '%" + parm.getStuName() + "%'");
        }
        //id
        sb.append("  and sc.course_id = ? and sc.teacher_id = ?");
        String sql = sb.toString();
        //2、定义handler
        ScalarHandler handler = new ScalarHandler();
        //3、定义参数
        Object[] parms = {
                parm.getCourseId(),
                parm.getTeacherId()
        };
        try {
            return Integer.valueOf(query.query(sql,handler,parms).toString());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return 0;
    }

    @Override
    public void addPoint(StudentPoint studentPoint) {
        //1、查询学生成绩是否存在
        StudentPoint point = studentPointDao.getById(studentPoint);
        //如果存在，删除
        if(point != null){
            studentPointDao.deleteById(point.getPointId());
        }
        //保存
        studentPointDao.add(studentPoint);
    }

    @Override
    public int getTotal() {
        String sql = "select count(teacher_id) as teacher_toatl from teacher ";
         ScalarHandler handler = new ScalarHandler();
        try {
           return Integer.valueOf(query.query(sql,handler).toString());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return 0;
    }

    @Override
    public Teacher getUserByPas(int userId, String password) {
        String sql = "select * from teacher where teacher_id =? and password=?";
        BeanHandler<Teacher> handler = new BeanHandler<>(Teacher.class,processor);
        Object[] parms = {
                userId,
                password
        };
        try {
            return query.query(sql,handler,parms);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    @Override
    public void updatePassword(int userId, String password) {
        String sql = "update teacher set password=? where teacher_id =?";
        Object[] parms = {
              password,
              userId
        };
        try {
            query.update(sql,parms);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
}
